package fr.utbm.lo53;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectionBD {
	public static ConnectionBD instance = null;
	public Connection connection = null;
	public static ConnectionBD getInstance() {
		if (instance == null) {
			instance = new ConnectionBD();
			instance.setConnection();
		}
		return instance;
	}
	public int getIdApByMac(String mac) {
		int idAP = 0;
		try {
			Statement sql = connection.createStatement();
			ResultSet resultIdAp = sql.executeQuery("SELECT id FROM ACCESSPOINT WHERE mac_addr = '" + mac + "'");
			if (resultIdAp.next()) {
				System.out.println("AP found");
				idAP = resultIdAp.getInt("id");
			}
			else {
				sql.executeUpdate("INSERT INTO ACCESSPOINT (mac_addr) VALUES ('" + mac + "')",Statement.RETURN_GENERATED_KEYS);
				ResultSet resultIdApInsert = sql.getGeneratedKeys();
				if (resultIdApInsert.next()) {
					idAP = resultIdApInsert.getInt(1);
				}
				System.out.println("AP not found");
			}
			//System.out.println("idAP = " + idAP);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return idAP;
	}
	private void setConnection() {
		try {
			Class.forName("org.postgresql.Driver");
		} 
		catch (ClassNotFoundException e) {
			System.out.println("Where is your PostgreSQL JDBC Driver? Include in your library path!");
			e.printStackTrace();
		}
		Connection connec= null;
		try {
			connec = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/lo53","lo53", "lo53");
		} 
		catch (SQLException e) {
			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
		}
		if (connec != null) {
			System.out.println("You made it, take control your database now!");
		}
		else {
			System.out.println("Failed to make connection!");
		} 
		connection = connec;
	}
	public int getIdLocByXY(double x, double y, int mapid) {
		int idLoc = 0;
		try {
			Statement sql = connection.createStatement();
			ResultSet resultIdLoc = sql.executeQuery("SELECT id FROM LOCATION WHERE x = " + x + " AND y = " + y);
			if (resultIdLoc.next()) {
				System.out.println("Loc found");
				idLoc = resultIdLoc.getInt("id");
			}
			else {
				sql.executeUpdate("INSERT INTO LOCATION (x,y,map_id) VALUES ( " + x + "," + y + "," + mapid + ")",Statement.RETURN_GENERATED_KEYS);
				ResultSet resultIdApInsert = sql.getGeneratedKeys();
				if (resultIdApInsert.next()) {
					idLoc = resultIdApInsert.getInt(1);
				}
				System.out.println("LOC not found");
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return idLoc;
	}
	public boolean existRssiValue(int idAP, int idLoc) {
		try {
			Statement sql = connection.createStatement();
			ResultSet resultrssi = sql.executeQuery("SELECT id_loc FROM RSSI WHERE id_loc = " + idLoc + " AND id_ap = " + idAP);
			if (resultrssi.next()) {
				return true;
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	public void insertRSSI(int idAP, int idLoc, double val) {
		if (!existRssiValue(idAP, idLoc)) {
			try {
				Statement sql = connection.createStatement();
				sql.executeUpdate("INSERT INTO RSSI (id_loc,id_ap,avg_val) VALUES ( " + idLoc + "," + idAP + "," + val + ")");
				System.out.println("RSSI INSERT");
			}
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		else {
			System.out.println("RSSI found");
			try {
				Statement sql = connection.createStatement();
				sql.executeUpdate("UPDATE RSSI SET avg_val = " + val + " WHERE id_loc = " + idLoc + " AND id_ap = " + idAP);
				System.out.println("RSSI UPDATE");
			}
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
}
